Computerized Investing - Spreadsheet Collection/Spreadsheet Collection.iso/pc/ibm12/portmgmt.doc

Text File | 1991-07-12 | 34.6 KB | 709 lines

A Portfolio Management Worksheet for Money Market Accounts, T-
Bills, Bonds and Stocks
By Fred Shipley, Ph.D.
Computerized Investing
March/April 1989 - September/October 1989

In managing a portfolio, an investor must keep track of the current
return, any current or anticipated changes in value, and the
riskiness of the portfolio.  In this article, we provide a template
to perform these calculations for a diversified portfolio, which
can include money market funds, T-bills, bonds and stocks.  In
addition, you can update these values over time and provide
periodic reports to measure performance.  This worksheet will track
annualized returns for the portfolio, considering the irregular
timing of cash inflows and outflows.  It will monitor the riskiness
of the individual portfolio components.  Finally, it will allow you
to make performance evaluation comparisons with other managed
portfolios or with the market.

Issues in Performance Measurement

The evaluation of portfolio performance requires an understanding
of both the returns and the risk in the portfolio.  Each of these
aspects of portfolio performance involves difficulties in
measurement.  For determining returns, a careful understanding of
the timing of cash inflows and their disposition--either reinvested
or spent--is critical.

Return on an investment is appropriately measured in terms of its
realized annually compounded rate of return.  This is the return
that will make the initial portfolio value accumulate to the known
final portfolio value over the elapsed number of years.  It is also
known as the internal rate of return.  Implicit in the use of this
concept is the presumption that any cash flows generated by the
investment are reinvested at that internal rate of return.  If you
estimate an annually compounded rate of return for an investment
and spend any part of the cash flows generated by dividends or
interest income, your realized rate of return will be lowered.  In
effect, spending a cash dividend is the same as spending principal,
which reduces the value of the portfolio.  This reduction, however,
must be treated in proportion to the current value of the
portfolio; therefore, the timing of cash inflows and outflows is
important.  To properly evaluate the change in portfolio return
over time, the value of the portfolio must be determined every time
a transaction occurs.

The easiest way to deal with this problem is analogous to the way
mutual funds use net asset value to determine the number of shares
an investor buys or sells.  At any time, the total value of the
portfolio is determined and the amount of a purchase or redemption
is divided by the net asset value per share to determine the number
of shares in the fund that are bought or sold.

Similarly, we will determine the value of a share in our portfolio
every time a transaction occurs.  The transaction will then have
the effect of either purchasing or redeeming a number of those
shares.  To keep the values easy to track, we will start with an
assumed initial portfolio share unit value of $1.  Tracking the
value of these shares of the portfolio allows us to determine the
annually compounded rate of return, while accounting for the cash
inflows and outflows that have occurred over time.  We can
determine the performance over the period between the most recent
transactions or compounded over a much longer period of time.

Evaluating the overall risk of the portfolio is conceptually
difficult.  There is no generally accepted indicator of overall
portfolio risk when that portfolio includes both equities and
bonds.  Nevertheless, we do have risk measures for each of these
components--beta for equities and duration for bonds.  For a money
market fund or account a zero beta is usually accepted.  We can
then examine performance in light of the risk measures for each
component of the portfolio.  A risk-return measure can be
determined for the equity and money market component of the
portfolio, which is compared to the market risk-return measure.
The return and duration of the bond portion of the portfolio may
be similarly compared.  Both the duration and beta measures appear
in the spreadsheet.

Setting Up the Input Data

In setting up the input data for your portfolio, we have designed
the spreadsheet to accommodate any number of different securities
and types of securities.  The spreadsheet is divided into four
major sections--money market investments, bonds (and bond mutual
funds), stocks (and stock mutual funds), and overall performance
evaluation.

In the example worksheet there are a few different securities in
each of the first three sections.  Each separate transaction,
whether the receipt of interest or dividend payments or the
purchase or sale of an asset, requires a new line of data.  While
this task can be daunting, we have made it easier by using a number
of the features that 1-2-3 (and other spreadsheets) provide.
Essentially, we add a row in the appropriate section of the
spreadsheet and fill in the relevant information.  By setting up
our formulas carefully, we can maintain the appropriate category
subtotals.

First, determine in what section you wish to add a security.  Place
your cursor at the row corresponding to the last entry in that
section.  As an example, we are going to add another stock.  Doing
so means placing the cursor in row 43.  Use the / Worksheet Insert
Row command to insert a row between the two stocks.  Then, to keep
the formatting that exists for the current section, use the Copy
command to copy the data from the first company down to the
inserted row.  At this point, simply write over the existing data
with data for the company you have added to your portfolio.

If you simply insert a row and enter data, you will have to format
each range to correspond to the way you want the data displayed.
That can take a long time.  If you want to enter more than one
company, simply insert as many rows as you need, and copy the first
company down all the inserted rows.

You might worry about getting companies in a random order with this
process; but it is simple to rearrange the companies in any order
you find convenient using the Data Sort command.  If you are only
entering one or two companies, you might find it easy to simply
insert the needed row(s) at appropriate spots in the spreadsheet.
When adding more than one or two, using the sort features will save
time.

Suppose, for example, you have the spreadsheet with UAL entered
between AT&T and Compaq.  Sorting the companies into alphabetical
order is straightforward.  First, type / Data Sort, and 1-2-3
prompts you to indicate the Data-Range.  Highlight the entire
equity section, A40..R43, and hit Return.  Then you can choose the
Primary-Key.  This will be the first key on which the sort will
occur--the company names, in this case.  Move the cursor to cell
B40 and strike Return.  Lotus then prompts you for the order in
which the data should be sorted--ascending or descending.  In this
case, ascending order goes from A to Z.  Type A, and you will be
back to the menu.  At this point simply typing G for Go (or
highlighting Go and hitting Return), will sort these companies into
alphabetical order.  You can use the same technique to sort on
ticker symbol or date of transaction, if you wish.

In addition, you can sort on more than one key.  For example, you
might want to have all securities in a section listed
alphabetically, and have different transactions for the same
security sorted by date.  To do this, simply specify the Secondary-
Key and highlight a cell with the transaction date in it.

There are some things to remember when doing this.  A certain
amount of care is necessary, since 1-2-3 overrides the existing
data in the worksheet.  It would be wise to save the file first.
In that way, anything you have done up to the point of sorting will
be preserved if you make a mistake.  Second, remember to highlight
every piece of information in the equity section.  Lotus only
rearranges information that is highlighted.  Suppose, for example,
you do not highlight all the way over to column R  but stop at
column G.  Then the information on transactions cost, total cost,
current market value, etc., will not be sorted and will not
correspond to the data for the correct company.  Unfortunately,
there will be nothing in the spreadsheet that will alert you to
this problem.

The Money Market Section

We put all money market type investments in this section.  You can
include cash, money market mutual funds, money market deposit
accounts, Treasury bills and certificates of deposit--any
investment with a maturity of one year or less.  Normally you would
put longer-term fixed-income investments, such as corporate or
municipal bonds, into the fixed-income section.  This section
requires you to enter the name of the issuer, the beta (zero), the
investment's original and current yield, its maturity as a date,
the original maturity in days, the current balance, the date of the
current transaction, and the amount of the current transaction.
Using this information, the spreadsheet determines the number of
days remaining to maturity and the percentage invested, both for
each item and as a weighted average for all money market
investments.  In addition, there are several columns (M through R)
which contain calculations necessary to make the section subtotals
work.

For the Treasury bills, we must determine the appropriate value
that we have invested.  We simply take the bid yield as reported
in the financial media and use that to determine the appropriate
price.  This is equivalent to the uncompounded yield reported for
money market funds.  These calculations appear in cells H16 and L16
and have been discussed in the January/February 1989 issue of CI.
Otherwise, enter the initial price directly in cell H16 and use the
discount yield calculation in cell D16.

The Bond and Bond Fund Section

The fixed-income section of the portfolio is patterned after the
bond portfolio management worksheet presented in the
January/February issue of Computerized Investing.  You must enter
the original purchase date, the issuer, the coupon, the maturity
date, the initial cost, number of units, any transactions costs
(commissions and transfer fees, for example), current price and the
date of each transaction.  The spreadsheet then determines the
current yield and yield to maturity, the duration, the total cost
and market value, the percentage invested in each, and the market
gain or loss on each.  Several columns of data calculations (P
through U) needed to compute the section subtotals then follow.

The Stock and Stock Fund Section

The equity part of the portfolio is designed to be similar to the
fixed-income section, but different issues are important here.  For
example, riskiness is measured by beta rather than by duration.

You must enter the initial purchase date, the company (or fund)
name and ticker symbol, the beta, quarterly dividend, cost per
share, transactions costs, number of shares, date of the current
transaction and current market value.  The program then determines
the annual dividend and dividend yield, total market value, overall
gain or loss and the percentage invested in each asset.  Finally,
there are several columns of data calculations.

The Overall Portfolio Section

This section of the spreadsheet summarizes the results of each of
the previous sections.  All of this information is calculated and
requires no data entry.  The summary information covers current
return, risk measures, portfolio allocation, and overall gain and
loss.  It offers a quick glimpse into the current status of your
portfolio.  To evaluate longer-term performance, we must take data
for each transaction and develop an ongoing record of performance.
This performance record enables us to evaluate our success (and
difficulties) over time.  Our example illustrates each of these
sections of the spreadsheet.

Overall performance measures.

An important aspect of portfolio management is determining return
in order to evaluate performance.  Once the information
corresponding to each portfolio transaction is entered into the
worksheet, that data can be carried down to create a summary report
and performance figures.  Benchmarks can then be established for
evaluating the portfolio's short-term performance, while keeping
in mind the importance of performance over the long run.  The
ability to generate short-run evaluations should not lead to making
short-run decisions.

In this article, we explain the concepts necessary to determine
portfolio performance measures.  In the next issue, we will take
you step by step through the updating process.  The spreadsheet
shows how the overall performance section will look.  This part of
the spreadsheet is for illustrative purposes only.  It illustrates
how the updating process affects overall evaluation.  The data here
is composed of sample numbers.  As we go through the updating
process in our next issue, we will create spreadsheet formulas to
generate these numbers.  Most of the information will be carried
down from previously entered data.  What remains to be added are
the figures used in making performance comparisons.

An Indexed Measure of Portfolio Performance

As mentioned in Part I (March/April CI), one way to track portfolio
performance over time, with periodic cash inflows and outflows, is
analogous to the way a mutual fund uses net asset value to
determine the number of shares an investor receives as the result
of a transaction.  Any time you make a portfolio transaction, you
must determine the total value of the portfolio and the proportion
of the portfolio affected by the transaction.  You accomplish this
by establishing a portfolio share unit value.  Tracking changes in
the value of these share units allows you to determine the periodic
rate of return and other performance measures.

To set this up, we will assume that our first transaction occurs
with a portfolio share unit value of $1.00.  We could use any
number, but it is much easier to understand the magnitude of
changes by starting with a value of $1.00.  In order to determine
the value of transactions, we must know the market value of the
portfolio when those transactions occur.  By dividing the current
portfolio market value by the initial value of our portfolio, we
establish a portfolio index value.

For example, if the first transaction we make in our portfolio is
the purchase of 200 shares of Delta Air Lines at $40 per share plus
transaction costs of $175 for a total of $8,175 on February 3,
1979.  The beginning portfolio value would be $0.00 since this is
our first transaction.  With $1.00 fixed as our beginning
transaction share unit value we have added 8,175 share units to the
portfolio. We want to determine the value and the return of the
portfolio on October 20, 1982, when we make another transaction--
the purchase of Georgia Power.  At this point, our Delta stock is
worth $54.00 a share, for a total portfolio value of $10,800.
Dividing that $10,800 beginning value by our beginning 8,175 share
units gives us a beginning share unit value of $1.321.

Determining the rate of return is then a matter of tracking the
change in the share unit value.  We examine the change in this
value, the time period over which the change occurred, and
determine the equivalent annual rate of change.  Because we must
evaluate each part of our portfolio separately, the value for every
component must be determined for transactions of any kind.

Determining the Annually Compounded Change in Portfolio Share Unit
Values

In our example, our next transaction is buying Georgia Power bonds
on October 20, 1982.  Over the period from February 3, 1979 to
October 20, 1982, a period of three years and eight months (1,355
days), our portfolio share unit value has increased from $1.000
(cell E66) to $1.321 (cell E67).  Using the date functions of your
spreadsheet, this is equivalent to a rate of 7.80% per year.  We
get this annual percentage rate of return (APR) with the formula:

APR = [(1 + R) ^ (365.25/# of days)] - 1

where:  (1 + R) = Ending Unit Value/Beginning Unit Value

For our example, the beginning unit value is $1.000 and the ending
unit value is $1.321.  This gives us the effective annual realized
rate of return of:

APR = [(1.321/1.000) ^ (365.25/1355)] - 1

    = 1.321 ^ (0.2696) - 1

    = 1.078 - 1

    = .078 or 7.8%

We will set up a column (K) for the most recent periodic change,
as well as a column (L) that will cumulate those period changes
over time.  These changes will be compared to market changes.

Obtaining Market Data for Comparative Purposes

Market data for comparing and evaluating portfolio performance can
be obtained from a variety of sources.  For most investors the Wall
Street Journal is the most readily available.  The Wall Street
Journal publishes performance information for various types of
investments on a quarterly basis.  While these reports appear every
quarter, there is no fixed date at which to expect them; you will
simply have to watch carefully.  These data includes total returns
for various stock market indexes and averages, bond market indexes,
and different managed investment portfolios--some private funds,
some mutual funds.  Similar information can be found in Barron's.
Ibbotson and Associates in Chicago offers a quarterly data service
with detailed information on unmanaged portfolios.  This service
is expensive for an individual investor, but public libraries in
major metropolitan areas and larger university libraries may have
subscriptions.  In addition, this information is available on-line
and can be downloaded, for investors who need the utmost
timeliness.

The stock and bond indexes that are published daily are based on
price changes only, and do not indicate total return.  While these
may be used for comparison with price changes in your portfolio,
performance must be judged on a risk-adjusted, total return basis.
It is not necessary to make these total return performance
evaluations on a daily basis--quarterly, or even annual, updating
is sufficient.  Your purpose here is to track the long-run
performance of your portfolio against some benchmarks.  Frequent
performance evaluations can generate a short-run trading mentality
that can lead to poor long-run performance.

You will need to find the return for Treasury bills and the return
for a market index to perform the risk-return evaluation for the
money market and equity portions of your portfolio.  The most
commonly used index of general market activity is the S&P 500.  If
you find an index that better represents your portfolio, feel free
to use it.

The bond portion of your portfolio should be compared with the
return on a portfolio of bonds similar to your own bond portfolio.
Ideally, the comparison should be made by matching duration, but
information on duration is difficult to obtain.  You can make a
useful comparison by tracking a bond index with a maturity matching
that of your bond portfolio.  Another way of judging performance
is to track the total return of a bond fund with approximately the
same maturity as your bond portfolio.  Morningstar publishes the
average maturity for bond mutual funds in Mutual Fund Values, a
financial reporting service for mutual funds that is similar to the
Value Line Investment Survey for common stocks.

Finally, to determine performance that is adjusted for inflation,
you should also track changes in the Consumer Price Index (CPI).
This is reported regularly in the media.

Evaluating performance should be done using long-run historical
results, as well as current data.  Table 2 gives long-run returns
based on historical data from the beginning of 1926.  This table
gives the historical values for the equity (or market) risk
premium, the return on the market less the Treasury bill return,
as well as the real risk-free rate of return, which is simply the
realized return on Treasury bills less the rate of inflation,
measured by the CPI.

                         Table 2

                                  Annually Compounded Returns

                                  1926˛1988         1971˛1988

Common Stocks                       10.0%             11.0%
   (S&P 500)
Treasury Bills                       3.5%              7.6%
Inflation                            3.1%              6.4%
   (CPI)
Long-Term Corporate Bonds            5.0%              8.8%
   (20-Year Maturity)
Long-Term Government Bonds           4.4%              8.4%
   (20-Year Maturity)
Intermediate-Term Governments        4.8%              8.8%
   (5-Year Maturity)
Equity Risk Premium                  6.2%*             3.1%*
Real Risk-free Return                0.5%*             1.2%*

* NOTE:  These figures are calculated as geometric averages.  For
example, to determine the equity risk premium for the years 1926
through 1988, 1 plus the common stock return is divided by 1 plus
the Treasury bill return and 1 is subtracted from that result.  The
formula is:

Equity Risk Premium  = (1 + Stock Return)/(1 + T-Bill Return)

                     = (1.10/1.035) - 1

                     = 1.062 - 1

                     = 0.062 or 6.2%

Similar calculations are done to determine the real risk-free
return.

End of Table 2

More detailed information can be found in "Stocks, Bonds, Bills and
Inflation, 1989 Yearbook," published by Ibbotson Associates,
Chicago.  The longer period figures are usually the benchmarks
against which institutional portfolio managers are judged.  The
data for the period since 1971 suggests significant changes in the
way the economy has operated and risk has been rewarded.  Higher
rates of inflation have led to a lower risk premium and higher
returns on fixed-income investments.  If inflation stabilizes, even
at the current 4% to 5% rate, the risk premium may well return to
the level that prevailed over the past 63 years.

Comparing Risk and Return

Comparing risk and return means judging how much return you
received for the risk taken.  This is done by comparing your return
with the return that could be generated without taking any risk.
The return on three-month Treasury bills is an appropriate measure
of the risk-free return.  Since we will be using an annually
compounded portfolio return, it is important to use an annually
compounded return on Treasury bills, also.  For comparison
purposes, however, the return should be measured over the period
you are evaluating.

One way to determine this return if you are making quarterly
performance evaluations is to use the effective annual return for
the T-bills that mature closest to your evaluation date.  A similar
process can be used for annual comparisons.  The coupon-equivalent
yield on outstanding Treasury bills is published daily in the Wall
Street Journal.  Since this rate is compounded semiannually, you
can easily convert it to an effective annual return.  Simply take
the yield, divide by two and compound the result for two periods.
For example, suppose the quoted yield is 8.9%.  Then the effective
annual yield is determined by:

APR = [(1 + Y/2) ^ 2] - 1

where:  Y is the quoted yield

APR = [(1 + 0.089/2) ^ 2] - 1

    = [(1.0445) ^ 2] - 1

    = 1.09098 - 1

    = 0.09098 or 9.098%

In addition, the Treasury bill return calculator from the
March/April issue of CI will provide a current value of that
return.  This approach assumes that you purchased a T-bill in the
beginning of the period and held it until its maturity.  The
effective annual return assumes that you reinvested the proceeds
at maturity at that return.  This is the same assumption applicable
to the portfolio rate of return.

The risk-adjusted return is simply the difference between the
return on your equity investments and the risk-free return, divided
by the beta, or riskiness, of your equity portfolio.

Return/Risk = (RP - RF)/Beta

where:  RP is the realized return on your portfolio,

        RF is the return on the risk-free T-bills, and

        Beta is the beta of your portfolio

The equity portfolio beta is the weighted average of the betas of
the equities and stock mutual funds in your portfolio.  The
percentage each investment represents of the total portfolio is
calculated and multiplied by the investment's beta.  These figures
are then added together to determine the portfolio beta.  This can
be found in column I, row 54 in the spreadsheet (see Figure 1).

Making a similar risk-adjusted calculation for the market will
indicate how you have done relative to the market.  Suppose, for
example, that during the most recent three months, your portfolio
has a total annualized return of 13%, Treasury bills have a return
of 8%, the S&P 500 index has a return of 11%, and the beta of your
portfolio is 0.90.  Then your risk-adjusted return is:

Return/Risk = (RP - RF)/Beta

            = (0.13 - 0.08)/0.90

            = 5.56%

Performing a similar calculation for the market gives a market
risk-adjusted return of 3%, since the beta of the market is 1.00.
By this standard, you earned more for the risk you took than the
market, so you outperformed the market.  If the risk-adjusted
return for your equity portfolio were less than 3%, you would have
underperformed the market.

You could perform a similar analysis for each investment in your
portfolio.  This analysis can be used for mutual funds, since they
are well-diversified and should only have market-related risk.
(Beta is a market-related risk measure.)  You should not, however,
use such a performance measure for individual stocks.  The betas
for individual stocks can vary over time, and so beta is not an
appropriate way to judge their performance.  Also, only about half
of a security's total risk is market-related and, therefore, only
half of the risk is captured by beta.

By tracking your portfolio over a period of time and making regular
comparison of its performance with market indicators, you will be
able to judge your investment record.  The spreadsheet will track
the numbers for you; you must judge their significance.  Overall,
performance should be commensurate with the level of portfolio
risk.

If your portfolio performance is considerably better than the
market's on a risk-adjusted basis, consider yourself an outstanding
investment manager. If not, evaluate whether or not  frequent
trading is generating excessive transactions costs and high taxes.
The spreadsheet tracks transaction costs for you, but it does not
track taxes.  Decreasing the rate of portfolio turnover and
focusing on long-term performance may improve your results.  For
most investors, increasing turnover is not the solution to poor
performance.

Updating Portfolio Values

In this article we conclude our series setting up a worksheet to
track, update and evaluate portfolio performance.  The focus of
this article is on the mechanics of portfolio updating and
performance evaluation.  The previous two articles set up the
worksheet and discussed the issues relevant to performance
evaluation.  There are a few additions to be made to the
spreadsheet at this point--primarily in setting up the performance
comparisons.

How Often Must You Update?

Computerized investors have the advantage of access to very current
securities prices and the ability to integrate that information
into their personal financial databases quickly.  The abundance of
current information may tempt you to track portfolio values on a
daily basis.  This is not necessary, though you may find it useful.

In order to determine appropriate portfolio performance measures,
though, you must update all securities' values every time a
portfolio transaction occurs.  Failure to do so will result in
errors in your rate of return calculations.  These errors may be
small, but they can accumulate over time and seriously misstate
performance.  If performance is misstated, you may make unfortunate
decisions when revising your portfolio holdings.

In addition to updating whenever a transaction occurs, most
investors will want to make quarterly and annual performance
evaluations.  These numbers should be used to judge performance and
make reallocation decisions.  Again, do not feel that revisions
must be made quarterly simply because you are evaluating
performance quarterly.  Excessive portfolio turnover generally
leads to lower realized returns.

Using the Portfolio Data

A list of benchmark performance figures is shown in Table 1.  As
the list of sources suggests, gathering the necessary information
requires some vigilance and a fair amount of work.  There is no
single source for all the necessary information.  Probably the most
difficult type of information to gather is that for fixed-income
securities.  There is some hope, however, as most of the major
financial papers are recognizing the importance of fixed-income
securities in investors' portfolio holdings.  Finding the return
is easy; it is finding the duration of these bond indexes that is
difficult.  Nevertheless, it is possible to obtain current
performance figures on a regular basis.  With a weighted-average
coupon and a weighted-average time to maturity, you can impute a
duration based on the current market yield-to-maturity as we have
done with the Morningstar figures.  The  bond portfolio management
article from the January/February 1989 issue of CI discusses
duration and gives the necessary formula.

                             Table 1
                   Realized and Current Return
                      Performance Benchmarks


                                         Realized
                                          Return         Current
                                         1st Half         Yield
Investment Type                            1989           8/89

3-month T-bills                           4.32%           8.27%
Taxable Money Market Funds                5.19%           8.12%
Non-Taxable Money Market Funds            3.50%

Shearson Lehman Hutton
   Gov't./Corp. Bond Index                9.23%
High Quality Corporate Bond Funds         7.89%           9.83%
   10.4 years to maturity; duration 6.9
Merrill Lynch 10-Yr. + Corps.            11.34%
   21.5 years to maturity; duration 8.9
Merrill Lynch 10-Yr. + Gov'ts.           13.62%

S&P 500 Index                            16.53%           3.24%
Dow Jones 30 Industrials                 14.95%           3.39%

Growth Stock Funds                       15.64%
Small Co. Funds                          15.30%


Sources:  Merrill Lynch Bond indexes appear in the Wall Street
Journal on a regular basis.  Mutual fund data from Morningstar's
Mutual Fund Values.  Figures for the S&P 500 and Dow Jones appear
in Barron's with the mutual fund data. Current yields appear each
week in the Market Laboratory section.

End of Table 1

In addition to the performance data in Table 1, we have also given
some current return data for some of the investments used as
benchmarks.  This current return data allows you to evaluate the
income status of your portfolio.  This information will be useful
when considering changes in portfolio composition.  You must
determine your own portfolio objectives to determine the importance
of current return in total return comparisons.  Comparison of the
current return figures with the historical averages and the figures
of the past quarter and year will provide an indication of whether
performance is reasonable or not.

Computing Performance Figures

We showed in the May/June issue how to compute a portfolio share
unit value to determine the overall portfolio total rate of return.

To make similar total return calculations for each part of the
portfolio, we must add a few columns of formulas to the right of
the section on Overall Portfolio Performance Measures.  Starting
in cell J52, enter the beginning portfolio value, date of update
(K52), beginning number of units (L52), the beginning unit value
(M52), change in portfolio market value (N52), cash withdrawals or
additions (O52), change in units (P52), ending number of share
units (Q52), and total portfolio value (R52).

The final step is to set up the overall risk-adjusted performance
measures immediately below the total portfolio return calculations.

Insert 20 rows starting at row 59, using the / Worksheet Row Insert
command.  The first four columns are the same as those above them-
-the current date, portfolio component, and name and value of risk
measure.  Next is the realized return over the quarter (column E).
Then in column F, enter the name of the benchmark portfolio.  In
column G, enter the return from that comparison portfolio.  In
column H, we calculate the risk-adjusted performance for the equity
section of the portfolio.  For the fixed-income securities,
reference the duration of the corresponding index in column H.  In
column I, calculate the risk-adjusted performance for the benchmark
portfolios.  In column J, enter the current yield of the market
benchmarks, and in column K do the same for each portfolio
component (from column E, rows 52 through 54).  Copying down the
dollars and percentage invested from cells F52 through G56 to cell
L64 will allow you to determine overall portfolio total realized
return and current yields, which can be compared with the current
market indexes.  Finally, enter the appropriate summary market
benchmark data in cells A75 through H78.  This completes the
performance evaluation section.

The Updating Process

To make this template work, simply enter the market values of each
security whenever a transaction occurs, such as a portfolio change,
the receipt of a dividend or interest payment, or cash withdrawal
or addition.  Remember that a dividend whose income is spent
represents a withdrawal from the portfolio since it is not
reinvested.  At the same time, enter the appropriate market
performance figures.

Once the current market values are entered, the portfolio
performance figures and market benchmarks are calculated.  Then you
can evaluate the realized performance and plan for the future.

At this point, you will find it useful to save the appropriate
quarterly performance figures.  Use the File Extract command to do
so; the sequence is / File Extract.  Then specify the range from
A47 to O68.  Finally, 1-2-3 requests a file name, 2ndqtr89, for
example.  Do not enter an extension (.wks), as 1-2-3 automatically
does so.  Each year you can combine the quarterly files to obtain
an annual performance evaluation.  Tracking this data for your
portfolio should enable you to make better portfolio decisions.

(c) Copyright 1989 by the
American Association of Individual Investors